MySQL 5.6 deployment

Requirements

Ubuntu 22.04 LTS

Prereqs / cleanup / packages

sudo apt update && sudo apt upgrade -y
sudo apt install -y wget libaio1 libncurses5 libtinfo5

(remove any existing MySQL)

sudo systemctl stop mysql || true
sudo apt remove --purge mysql-server mysql-client mysql-common -y || true
sudo rm -rf /usr/local/mysql /var/lib/mysql /etc/mysql || true

Download and extract the generic tarball (official archive):

cd /tmp
wget https://downloads.mysql.com/archives/get/p/23/file/mysql-5.6.51-linux-glibc2.12-x86_64.tar.gz
tar -xzf mysql-5.6.51-linux-glibc2.12-x86_64.tar.gz -C /usr/local/
mv /usr/local/mysql-5.6.51-linux-glibc2.12-x86_64 /usr/local/mysql

Create mysql user/group and set ownership:

sudo groupadd -f mysql
sudo useradd -r -g mysql -s /bin/false mysql

sudo mkdir -p /var/lib/mysql
sudo chown -R mysql:mysql /var/lib/mysql
sudo chmod 750 /var/lib/mysql

sudo chown -R mysql:mysql /usr/local/mysql

sudo mkdir -p /var/run/mysqld
sudo chown mysql:mysql /var/run/mysqld
sudo chmod 755 /var/run/mysqld

sudo mkdir -p /var/log/mysql
sudo chown mysql:mysql /var/log/mysql
sudo chmod 755 /var/log/mysql

Add binaries to PATH:

sudo ln -s /usr/local/mysql/bin/mysql /usr/bin/mysql
sudo ln -s /usr/local/mysql/bin/mysqld_safe /usr/bin/mysqld_safe

echo 'export PATH=/usr/local/mysql/bin:$PATH' | sudo tee /etc/profile.d/mysql.sh
sudo chmod +x /etc/profile.d/mysql.sh
source /etc/profile.d/mysql.sh

Initialize the data directory:

sudo /usr/local/mysql/scripts/mysql_install_db --user=mysql --basedir=/usr/local/mysql --datadir=/var/lib/mysql

Setup systemd service

sudo nano /etc/systemd/system/mysql.service

File content:

[Unit]
Description=MySQL 5.6 Server
After=network.target

[Service]
User=mysql
Group=mysql
ExecStart=/usr/local/mysql/bin/mysqld --defaults-file=/etc/my.cnf --basedir=/usr/local/mysql --datadir=/var/lib/mysql
ExecStop=/usr/local/mysql/bin/mysqladmin shutdown
LimitNOFILE=102400

PIDFile=/var/run/mysqld/mysqld.pid
Restart=on-failure
LimitNOFILE=102400
[Install]
WantedBy=multi-user.target

Enable service:

sudo systemctl daemon-reload
sudo systemctl enable mysql
sudo systemctl start mysql
sudo systemctl status mysql

Set root password + run secure script:

sudo /usr/local/mysql/bin/mysql_secure_installation

CNF File (Master):

[mysqld]

#########################
# Memory & Buffer Pool  #
#########################
innodb_buffer_pool_size = 190G  # Allocating 190G for InnoDB buffer pool (optimal for 236GB RAM)
innodb_buffer_pool_instances = 32  # Distributes buffer pool workload across instances

###############################
# InnoDB Log & Transaction Log #
###############################
innodb_log_file_size = 24G  # Increase log file size to reduce I/O bottlenecks
innodb_log_files_in_group = 2  # Two log files for better write performance
innodb_log_buffer_size = 512M  # Larger buffer reduces frequent log writes
innodb_flush_log_at_trx_commit = 2  # Ensures durability by flushing logs at every transaction commit
innodb_flush_method = O_DIRECT  # Avoids double buffering for better disk performance

innodb_print_all_deadlocks = 1
innodb_io_capacity = 30000
innodb_io_capacity_max = 50000

######################
# Threading & Concurrency
######################
innodb_thread_concurrency = 19  # Set to half the number of CPU cores for balanced concurrency
innodb_read_io_threads = 16  # Number of background read I/O threads
innodb_write_io_threads = 16  # Number of background write I/O threads
thread_cache_size = 500  # Caches threads to improve connection handling

######################
# Query & Sorting Buffers
######################
join_buffer_size = 32M  # Buffer for joins without indexes
sort_buffer_size = 16M  # Buffer for sorting operations
read_buffer_size = 16M  # Buffer for sequential table scans
read_rnd_buffer_size = 4M  # Buffer for random reads

# temp
innodb_sort_buffer_size = 64M

######################
# Table & Index Caching
######################
table_open_cache = 5000  # Increase table cache to avoid frequent opening/closing of tables
open_files_limit = 102400  # Allow a high number of open files (needed for high concurrency)

######################
# Connection Handling
######################
skip-name-resolve  # Disable DNS lookups for faster connection handling
skip-external-locking  # Prevents external file locking (improves performance)
datadir=/var/lib/mysql  # MySQL data directory
tmpdir = /var/lib/mysql/mysql_tmp
pid-file=/var/run/mysqld/mysqld.pid  # PID file for process management
socket=/var/lib/mysql/mysql.sock  # MySQL socket file
bind-address = 0.0.0.0  # Listen on all interfaces (ensure firewall rules are in place)
connect_timeout = 60  # Time (seconds) before dropping an unresponsive connection
interactive_timeout = 300  # Close interactive connections after 300 seconds of inactivity
max_allowed_packet = 4096M  # Allow very large packet sizes (for bulk operations)
max_connections = 1000  # Maximum allowed concurrent connections

wait_timeout=3600

######################
# Query Cache (Disabled)
######################
query_cache_type = OFF  # Fully disable query cache (deprecated and causes locking issues)
query_cache_size = 0  # No memory allocated to query cache
query_cache_limit = 0  # Prevents accidental cache usage

######################
# Logging & Monitoring
######################
slow_query_log = 1  # Enable slow query logging
slow_query_log_file = /var/log/mysql/mysql-slow.log  # Path for slow query log
long_query_time = 10  # Log all queries for analysis
log_output = FILE  # Store logs as files
#log_slow_admin_statements = ON  # Uncomment if needed
#log_slow_slave_statements = ON  # Uncomment if needed
log_queries_not_using_indexes = ON  # Identify queries missing indexes
#log_throttle_queries_not_using_indexes = 10  # Uncomment if needed
log-error=/var/log/mysql/error.log #
log-warnings = 2
######################
# Security & Performance Tweaks
######################
symbolic-links = 0  # Disabling symbolic links for security reasons
sql_mode = NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES  # Recommended MySQL SQL modes

######################
# Replication - Master
######################
server-id = 1
log-bin=mysql-bin
expire-logs-days = 5
max-binlog-size = 1G
binlog_format = ROW
sync_binlog=1

# only replicate unfepi dbs, skip smstarseel and mysql and sys
binlog-do-db = unfepi
binlog-do-db = unfepi_dwh

[mysqld_safe]
# log-error=/var/log/mysql/mysqld_safe.log  # Location for general MySQL errors

[client]
socket=/var/lib/mysql/mysql.sock

CNF File (Replica):

[mysqld]

#########################
# Memory & Buffer Pool  #
#########################
innodb_buffer_pool_size = 160G  # Allocating 190G for InnoDB buffer pool (optimal for 236GB RAM)
innodb_buffer_pool_instances = 32  # Distributes buffer pool workload across instances

###############################
# InnoDB Log & Transaction Log #
###############################
innodb_log_file_size = 24G  # Increase log file size to reduce I/O bottlenecks
innodb_log_files_in_group = 2  # Two log files for better write performance
innodb_log_buffer_size = 512M  # Larger buffer reduces frequent log writes
innodb_flush_log_at_trx_commit = 2  # Ensures durability by flushing logs at every transaction commit
innodb_flush_method = O_DIRECT  # Avoids double buffering for better disk performance

innodb_print_all_deadlocks = 1
innodb_io_capacity = 30000
innodb_io_capacity_max = 50000

######################
# Threading & Concurrency
######################
innodb_thread_concurrency = 0  # Set to half the number of CPU cores for balanced concurrency
innodb_read_io_threads = 16  # Number of background read I/O threads
innodb_write_io_threads = 16  # Number of background write I/O threads
thread_cache_size = 500  # Caches threads to improve connection handling

######################
# Query & Sorting Buffers
######################
join_buffer_size = 32M  # Buffer for joins without indexes
sort_buffer_size = 16M  # Buffer for sorting operations
read_buffer_size = 16M  # Buffer for sequential table scans
read_rnd_buffer_size = 4M  # Buffer for random reads

# temp
innodb_sort_buffer_size = 64M
#sort_buffer_size = 64M

######################
# Table & Index Caching
######################
table_open_cache = 5000  # Increase table cache to avoid frequent opening/closing of tables
open_files_limit = 102400  # Allow a high number of open files (needed for high concurrency)

######################
# Connection Handling
######################
skip-name-resolve  # Disable DNS lookups for faster connection handling
skip-external-locking  # Prevents external file locking (improves performance)
datadir=/var/lib/mysql  # MySQL data directory
tmpdir = /var/lib/mysql/mysql_tmp
socket=/var/run/mysqld/mysqld.sock  # MySQL socket file
bind-address = 0.0.0.0  # Listen on all interfaces (ensure firewall rules are in place)
connect_timeout = 60  # Time (seconds) before dropping an unresponsive connection
interactive_timeout = 300  # Close interactive connections after 300 seconds of inactivity
max_allowed_packet = 4096M  # Allow very large packet sizes (for bulk operations)
max_connections = 1000  # Maximum allowed concurrent connections

wait_timeout=3600

######################
# Query Cache (Disabled)
######################
query_cache_type = OFF  # Fully disable query cache (deprecated and causes locking issues)
query_cache_size = 0  # No memory allocated to query cache
query_cache_limit = 0  # Prevents accidental cache usage

######################
# Logging & Monitoring
######################
slow_query_log = 1  # Enable slow query logging
slow_query_log_file = /var/log/mysql/mysql-slow.log  # Path for slow query log
long_query_time = 10  # Log all queries for analysis
log_output = FILE  # Store logs as files
#log_slow_admin_statements = ON  # Uncomment if needed
#log_slow_slave_statements = ON  # Uncomment if needed
#log_queries_not_using_indexes = ON  # Identify queries missing indexes
#log_throttle_queries_not_using_indexes = 10  # Uncomment if needed
log-error=/var/log/mysql/error.log #
log-warnings = 2
######################
# Security & Performance Tweaks
######################
symbolic-links = 0  # Disabling symbolic links for security reasons
sql_mode = NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES  # Recommended MySQL SQL modes

######################
# Replication - Slave
######################
server-id = 2
relay-log=relay-bin
read_only=1
sync_binlog=0

#ignore auditspcall table from unfepi_dwh
replicate-ignore-table = unfepi_dwh.auditspcall



[mysqld_safe]
# log-error=/var/log/mysql/mysqld_safe.log  # Location for general MySQL errors
pid-file=/var/run/mysqld/mysqld.pid  # PID file for process management

Requirements​

Prereqs / cleanup / packages​

(remove any existing MySQL)​

Download and extract the generic tarball (official archive):​

Create mysql user/group and set ownership:​

Add binaries to PATH:​

Initialize the data directory:​

Setup systemd service​

File content:​

Enable service:​

Set root password + run secure script:​

CNF File (Master):​

CNF File (Replica):​

Requirements

Prereqs / cleanup / packages

(remove any existing MySQL)

Download and extract the generic tarball (official archive):

Create mysql user/group and set ownership:

Add binaries to PATH:

Initialize the data directory:

Setup systemd service

File content:

Enable service:

Set root password + run secure script:

CNF File (Master):

CNF File (Replica):